package com.borun.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.borun.bean.Word;
import com.borun.bean.page.Page;
import com.borun.dao.base.Dao;
import com.borun.util.DBUtil;

public class WordDao implements Dao<Word> {

	@Override
	public boolean add(Word model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "insert into t_word(english,chinese,base_id) values(?,?,?)";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setString(1, model.getEnglish());
		psmt.setString(2, model.getChinese());
		psmt.setInt(3, model.getBase_id());
		return psmt.executeUpdate() > 0;
	}

	@Override
	public boolean delete(Integer id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "delete from t_word where id = ?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, id);
		return psmt.executeUpdate() > 0;
	}

	@Override
	public boolean update(Word model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("update t_word set ");
		if (model.getEnglish() != null) {
			sql.append("english = ?,");
			map.add(model.getEnglish());
		}

		if (model.getChinese() != null) {
			sql.append("chinese = ?,");
			map.add(model.getChinese());
		}

		if (model.getBase_id() != 0) {
			sql.append("base_id = ?,");
			map.add(model.getBase_id());
		}
		sql.delete(sql.length() - 1, sql.length());
		sql.append(" where id = ?");
		map.add(model.getId());
		psmt = conn.prepareStatement(sql.toString());

		for (int i = 0; i < map.size(); i++) {
			psmt.setObject(i + 1, map.get(i));
		}

		return psmt.executeUpdate() > 0;
	}

	@Override
	public Word findById(Integer id) throws SQLException {

		Word w = null;
		Connection conn = DBUtil.getConnection();
		String sql = "select id,english,chinese,base_id from t_word where id = ?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, id);

		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			w = new Word();
			w.setId(rs.getInt("id"));
			w.setEnglish(rs.getString("english"));
			w.setChinese(rs.getString("chinese"));
			w.setBase_id(rs.getInt("base_id"));
		}
		return w;
	}

	@Override
	public Word findBy(Word model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		Word w = null;
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"select top 1 id,english,chinese,base_id from t_word where 1 = 1 ");
		if (model.getEnglish() != null) {
			sql.append(" and english like ? ");
			map.add("%" + model.getEnglish() + "%");
		}

		if (model.getChinese() != null) {
			sql.append(" and chinese like ? ");
			map.add("%" + model.getChinese() + "%");
		}

		if (model.getBase_id() != 0) {
			sql.append(" and base_id = ? ");
			map.add(model.getBase_id());
		}

		psmt = conn.prepareStatement(sql.toString());

		for (int i = 0; i < map.size(); i++) {
			psmt.setObject(i + 1, map.get(i));
		}

		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			w = new Word();
			w.setId(rs.getInt("id"));
			w.setEnglish(rs.getString("english"));
			w.setChinese(rs.getString("chinese"));
			w.setBase_id(rs.getInt("base_id"));
		}
		return w;
	}

	@Override
	public List<Word> getList() throws SQLException {
		Connection conn = DBUtil.getConnection();
		Statement stmt = null;
		String sql = "select id,english,chinese,base_id from t_word where 1 = 1 ";
		stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		List<Word> wordList = new ArrayList<Word>();
		Word w = null;
		while (rs.next()) { // 如果对象中有数据，就会循环打印出来
			w = new Word();
			w.setId(rs.getInt("id"));
			w.setEnglish(rs.getString("english"));
			w.setChinese(rs.getString("chinese"));
			w.setBase_id(rs.getInt("base_id"));
			wordList.add(w);
		}
		return wordList;
	}

	@Override
	public List<Word> getListBy(Word model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"select id,english,chinese,base_id from t_word where 1 = 1 ");
		if (model.getEnglish() != null) {
			sql.append(" and english like ? ");
			map.add("%" + model.getEnglish() + "%");
		}

		if (model.getChinese() != null) {
			sql.append(" and chinese like ? ");
			map.add("%" + model.getChinese() + "%");
		}

		if (model.getBase_id() != 0) {
			sql.append(" and base_id = ? ");
			map.add(model.getBase_id());
		}

		psmt = conn.prepareStatement(sql.toString());

		for (int i = 0; i < map.size(); i++) {
			psmt.setObject(i + 1, map.get(i));
		}

		ResultSet rs = psmt.executeQuery();
		List<Word> wordList = new ArrayList<Word>();
		Word w = null;
		while (rs.next()) { // 如果对象中有数据，就会循环打印出来
			w = new Word();
			w.setId(rs.getInt("id"));
			w.setEnglish(rs.getString("english"));
			w.setChinese(rs.getString("chinese"));
			w.setBase_id(rs.getInt("base_id"));
			wordList.add(w);
		}
		return wordList;
	}

	@Override
	public List<Word> getPagedList(Page page) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select id,english,chinese,base_id from t_word where 1 = 1 limit ?,? ";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, page.getPageIndex());
		psmt.setInt(2, page.getPageSize());
		ResultSet rs = psmt.executeQuery();
		List<Word> wordList = new ArrayList<Word>();
		Word w = null;
		while (rs.next()) { // 如果对象中有数据，就会循环打印出来
			w = new Word();
			w.setId(rs.getInt("id"));
			w.setEnglish(rs.getString("english"));
			w.setChinese(rs.getString("chinese"));
			w.setBase_id(rs.getInt("base_id"));
			wordList.add(w);
		}
		return wordList;
	}

	@Override
	public List<Word> getPagedListBy(Page page, Word model) throws SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement psmt = null;
		ArrayList<Object> map = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"select id,english,chinese,base_id from t_word where 1 = 1 ");
		if (model.getEnglish() != null) {
			sql.append(" and english like ? ");
			map.add("%" + model.getEnglish() + "%");
		}

		if (model.getChinese() != null) {
			sql.append(" and chinese like ? ");
			map.add("%" + model.getChinese() + "%");
		}

		if (model.getBase_id() != 0) {
			sql.append(" and base_id = ? ");
			map.add(model.getBase_id());
		}

		sql.append(" limit ?,? ");
		map.add(page.getPageIndex());
		map.add(page.getPageSize());
		System.out.println(sql.toString());
		psmt = conn.prepareStatement(sql.toString());
		System.out.println(sql.toString());

		for (int i = 0; i < map.size(); i++) {
			psmt.setObject((i + 1), map.get(i));
		}

		ResultSet rs = psmt.executeQuery();
		List<Word> wordList = new ArrayList<Word>();
		Word w = null;
		while (rs.next()) { // 如果对象中有数据，就会循环打印出来
			w = new Word();
			w.setId(rs.getInt("id"));
			w.setEnglish(rs.getString("english"));
			w.setChinese(rs.getString("chinese"));
			w.setBase_id(rs.getInt("base_id"));
			wordList.add(w);
		}
		return wordList;
	}
}
